<?php
define('SHOPGATE_PLUGIN_REVISION', "\$Rev: 817 $");
define('SHOPGATE_PLUGIN_VERSION', "1.5.0");

require_once dirname(__FILE__).'/../includes.php';

/**
 * GambioGX Plugin für Shopgate
 *
 *  Beruhend auf dem offiziellen xt:c Plugin von Shopgate
 *  Modifikationen durch Kai de Haan
 *
 *  Die defaine-Variablen werden von Gambio gesetzt und
 *  stehen in der configure-tabelle.
 *
 *  Folgende define-Varibalen werden von gambio gesetzt:
 *  - STOCK_CHECK
 *  - STOCK_ALLOW_CHECKOUT
 *  - ATTRIBUTE_STOCK_CHECK
 *
 * @author Martin Weber
 * @author ranketing GmbH - Kai de Haan
 * @version 1.5.0
 * @package plugins
 */
class ShopgatePlugin extends ShopgatePluginCore {

	private $language = "DE";
	private $currency = "EUR";
	
	private $oCurreny = null;
	
	private $pluginPath;

	private $languageId;
	private $countryId;
	private $zoneId;

	/**
	 * (non-PHPdoc)
	 * @see ShopgatePluginCore::startup()
	 */
	public function startup() {
		global $gmLangFileMaster, $lng;

		$requiredFiles = array(
			'inc/xtc_validate_password.inc.php',
			'inc/xtc_format_price_order.inc.php',
			'inc/xtc_db_prepare_input.inc.php',
			'inc/xtc_get_tax_class_id.inc.php',
			'includes/classes/xtcPrice.php',
			'gm/classes/GMSEOBoost.php',
			'includes/modules/shipping/dp.php',
			'gm/inc/gm_get_conf.inc.php',
			'inc/xtc_get_products_stock.inc.php',
			'inc/xtc_get_products_stock.inc.php',
			DIR_WS_CLASSES.'language.php'
		);

		require_once 'system/core/Debugger.inc.php';
		$GLOBALS['coo_debugger'] = new Debugger();
		
		$this->log("Folgende Dateien werden includiert:\n".print_r($requiredFiles,true), "success");

		foreach($requiredFiles as $file) {
			require_once($file);
		}
		
		$this->zoneId = $this->config["tax_zone_id"];

		// Fetch Country
		$qry = "SELECT * FROM `".TABLE_COUNTRIES."` WHERE UPPER(countries_iso_code_2) = UPPER('".$this->config['plugin_country']."')";
		$qry = xtc_db_fetch_array(xtc_db_query($qry));
		$this->countryId = $qry ? $qry["countries_id"] : 'DE';
		
		// Fetch Language
		$qry = "SELECT * FROM `".TABLE_LANGUAGES."` WHERE UPPER(code) = UPPER('".$this->config['plugin_language']."')";
		$qry = xtc_db_fetch_array(xtc_db_query($qry));
		$this->languageId = $qry ? $qry["languages_id"] : 2;
		$languageDir = $qry ? $qry["directory"] : 'german';
		
		// Fetch exchange-rate
		$qry = "SELECT * FROM `".TABLE_CURRENCIES."` WHERE UPPER(code) = UPPER('".$this->config['plugin_currency']."')";
		$qry = xtc_db_fetch_array(xtc_db_query($qry));
		$this->exchangeRate = $qry ? $qry["value"] : 1;
		$this->currencyId = $qry ? $qry["currencies_id"] : 1;
		$this->oCurreny = $qry ? $qry : array("symbol_right" => "EUR");
		
		if(!defined("DIR_FS_LANGUAGES"))
			define('DIR_FS_LANGUAGES', DIR_FS_CATALOG. 'lang/');
		
		$langFiles = array(
			"admin/categories.php",
			"admin/content_manager.php",
		);
		
		foreach($langFiles as $langFile) {
			include_once DIR_FS_LANGUAGES."/$languageDir/$langFile";
		}
		
		return true;
	}

	/**
	 * (non-PHPdoc)
	 * @see ShopgatePluginCore::itemsAddAll()
	 */
	protected function createItemsCsv() {
		$gmSEOBoost = new GMSEOBoost();

		$qry = "
			SELECT DISTINCT
				p.products_id,
				p.products_ean,
				p.products_model,
				p.products_ean,
				p.products_quantity,
				p.products_image,
				p.products_price,
				sp.specials_new_products_price,
				DATE_FORMAT(p.products_last_modified, '%Y-%m-%d') as products_last_modified,
				p.products_weight,
				p.products_status,
				pdsc.products_keywords,
				pdsc.products_name,
				pdsc.products_description,
				shst.shipping_status_name,
				mf.manufacturers_name,
				p.products_tax_class_id,
				p.products_vpe_status,
				vpe.products_vpe_name,
				p.products_vpe_value,
				p.gm_show_qty_info,
				p.products_fsk18,
				p.products_sort,
				p.products_startpage,
				p.gm_show_weight
			FROM ".TABLE_PRODUCTS." p
			JOIN ".TABLE_PRODUCTS_DESCRIPTION." pdsc ON (p.products_id = pdsc.products_id)
			LEFT JOIN ".TABLE_SHIPPING_STATUS." shst ON (p.products_shippingtime = shst.shipping_status_id AND shst.language_id = pdsc.language_id)
			LEFT JOIN ".TABLE_MANUFACTURERS." mf ON (mf.manufacturers_id = p.manufacturers_id)
			LEFT JOIN ".TABLE_SPECIALS." sp ON (sp.products_id = p.products_id AND sp.status = 1 AND sp.specials_quantity > 0 AND (sp.expires_date > now() OR sp.expires_date = timestamp(0)))
			LEFT JOIN ".TABLE_PRODUCTS_VPE." vpe ON (vpe.products_vpe_id = p.products_vpe AND vpe.language_id = pdsc.language_id)
			WHERE pdsc.language_id = '".$this->languageId."'
			  AND p.products_status = 1
		";

		if(STOCK_CHECK == "true" && STOCK_ALLOW_CHECKOUT != 'true') {
			$qry .= " AND p.products_quantity > 0 ";
		}

		$maxId = xtc_db_fetch_array( xtc_db_query("SELECT MAX(products_id) max_id FROM ".TABLE_PRODUCTS) );
		$maxId = $maxId["max_id"];

		$maxOrder = xtc_db_fetch_array( xtc_db_query("SELECT MAX(products_sort) max_order FROM ".TABLE_PRODUCTS) );
		$maxOrder = $maxOrder["max_order"];

		$limit = 100;
		$offset = 1;

		$_qry = $qry . " AND p.products_id BETWEEN $offset AND " . ($offset + $limit);
		while($offset <= $maxId && $query = xtc_db_query($_qry)) {
			while($item = xtc_db_fetch_array($query)) {

				$categories = $this->getProductPath($item["products_id"]);

				$tax_rate = xtc_get_tax_rate($item["products_tax_class_id"], $this->countryId, $this->zoneId);
				$variations = $this->getVariations($item["products_id"], $tax_rate);

				$images = $this->getProductsImages($item);

				$productStatus = $item["products_status"];
				$productQuantity = $item['products_quantity'];
				// keine Produkte ohne Kategoriezurodnung importieren

				$httpServer = HTTP_SERVER;
				if(empty($httpServer)) $httpServer = "http://".$_SERVER["HTTP_HOST"];
				$deeplink = $httpServer.DIR_WS_CATALOG."product_info.php?products_id=".$item["products_id"];

				$items = array();
				$price = $item["products_price"];
				if(!empty($item["specials_new_products_price"])) {
					$price = $item["specials_new_products_price"];
				}

				$price = $price*(1+($tax_rate/100));
				$price *= $this->exchangeRate;

				$catIds = $this->getProductCategoriesIds($item["products_id"]);
				
				$tags = "";
				$tags = $item["products_keywords"];
				if($tags) $tags .= ($item["products_model"]?", ".$item["products_model"]:"");
				else $tags .= ($item["products_model"]?$item["products_model"]:"");
				
				$itemArr = $this->buildDefaultProductRow();
				$itemArr['item_number'] = $item["products_id"];
				$itemArr['manufacturer'] = $item["manufacturers_name"];
				$itemArr['item_name'] = trim($item["products_name"]);
				$itemArr['description'] =  preg_replace("/\n|\r/", "", $this->parseDescription($item["products_description"]));
				$itemArr['unit_amount'] = $this->formatPriceNumber($price);
				$itemArr['is_available'] = $productStatus;
				$itemArr['available_text'] = (string) $item["shipping_status_name"];
				$itemArr['url_deeplink'] = $deeplink;
				$itemArr['urls_images'] = $images;
				$itemArr['use_stock'] = STOCK_CHECK=='true'?1:0;
				$itemArr['stock_quantity'] = $productQuantity;
				$itemArr['categories'] = $categories;
				$itemArr['category_numbers'] = implode("||", $catIds);
				$itemArr['weight'] = $item["products_weight"]*1000;
				$itemArr['tags'] = $tags;
				$itemArr['currency'] = $this->config["plugin_currency"];
				$itemArr['tax_percent'] = $tax_rate;
				$itemArr['ean'] = preg_replace("/\s+/i",'',$item["products_ean"]);
				$itemArr['last_update'] = $item["products_last_modified"];
				$itemArr['basic_price'] = $this->getProductVPE($item, $price);
				$itemArr['properties'] = $this->buildProperties($item);
				$itemArr['age_rating'] = $item["products_fsk18"] == 1 ? '18' : '';
				$itemArr['sort_order'] = $maxOrder - $item["products_sort"];
				$itemArr['is_highlight'] = $item["products_startpage"];
				$itemArr['highlight_order_index'] = $item["products_startpage_sort"];
				$itemArr['related_shop_item_numbers'] = $this->getRelatedShopItems($item["products_id"]);

				if($itemArr['available_text'] == 'Unbekannt') {
					$itemArr['is_available'] = 0;
				}

				if(!empty($variations)) {
					if($variations["has_options"]) {
						$itemArr['has_options']=1;
						$this->addItem(array_merge($itemArr, $variations));
					} else {
						$itemArr['has_children']=1;
						$itemNumber = $itemArr["item_number"];
						$basePrice = $itemArr["unit_amount"];
						$baseWeight = $itemArr["weight"];

						$parentItemNumber = $itemArr["item_number"];
						$isFirst = true;
						foreach ($variations as $variation) {
							$price = 0;
							$weight = 0;
							if(!empty($variation["offset_amount"]))
							$price = $variation["offset_amount"]*(1+($tax_rate/100));

								
							if(isset($variation["offset_weight"]))
							$weight = $variation["offset_weight"] * 1000;

							$hash = "";

							for($i=1; $i < 5 && isset($variation["attribute_$i"]); $i++) {
								$hash .= $variation["attribute_$i"];

								$itemArr["attribute_$i"] = $variation["attribute_$i"];
							}
								
							$hash = md5($hash);
							$hash = substr($hash, 0, 5);
							if(empty($variation)) $variation = array("order_info" => array());
								
							$variation["order_info"]["base_item_number"] = $itemNumber;
								
							if(isset($variation["images"])) {
								$itemArr["urls_images"] = implode("||", $variation["images"]);
							}
								
							$itemArr['internal_order_info'] = json_encode($variation["order_info"]);
							$itemArr["item_number"] = $itemNumber.($isFirst?"":"_".$hash);
							$itemArr["unit_amount"] = $this->formatPriceNumber($basePrice + $price);
							$itemArr["weight"]		= $baseWeight + $weight;

							$items[] = $itemArr;
							$isFirst = false;
							$itemArr['has_children']=0;
							$itemArr["parent_item_number"] = $parentItemNumber;
						}
					}
				} else {
					$itemArr['has_children']=0;
					$items[] = $itemArr;
				}

				foreach($items as $item)
				$this->addItem($item);
			}

			$offset += $limit + 1;
			$_qry = $qry . " AND p.products_id BETWEEN $offset AND " . ($offset + $limit);
		}
	}
	
	private $maxCategorieSortOrder = 0;
	
	protected function createCategoriesCsv() {
		$qry = "SELECT MAX( sort_order ) sort_order FROM " . TABLE_CATEGORIES;
		$qry = xtc_db_fetch_array( xtc_db_query( $qry ) );
		$this->maxCategorieSortOrder = $qry["sort_order"] + 1;
		
		$this->buildCategoriesTree();
	}
	
	protected function buildCategoriesTree($parentId = 0) {
		$qry = "
			SELECT DISTINCT
			c.categories_id,
			c.parent_id,
			c.categories_image,
			c.categories_status,
			c.sort_order,
			cd.categories_name
			FROM ".TABLE_CATEGORIES." c
			LEFT JOIN ".TABLE_CATEGORIES_DESCRIPTION." cd ON (c.categories_id = cd.categories_id AND cd.language_id = $this->languageId)
			WHERE c.parent_id = $parentId
		";
				
		$qry = xtc_db_query( $qry );
		
		while($item = xtc_db_fetch_array( $qry )) {
			$row = $this->buildDefaultCategoryRow();
			
			$row["category_number"] = $item["categories_id"];
			$row["parent_id"] = $item["parent_id"]==0?"":$item["parent_id"];
			$row["category_name"] = $item["categories_name"];
				
			if(!empty($item["categories_image"]))
			$row["url_image"] = HTTP_SERVER.DIR_WS_CATALOG.DIR_WS_IMAGES."categories/".$item["categories_image"];
				
			$row["order_index"] = $this->maxCategorieSortOrder - (int) $item["sort_order"];
				
			$row["is_active"] = $item["categories_status"];
			$row["url_deeplink"] = HTTP_SERVER.DIR_WS_CATALOG."index.php?cat=c" . $item["categories_id"];
			
			$this->addItem($row);
			
			$this->buildCategoriesTree($item["categories_id"]);
		}
	}

	private function getProductVPE($product, $price) {
		$vpe = "";

		if($product["products_vpe_status"] == 1)
		
		if($product["products_vpe_value"] != 0)
			$price = 	$price * (1 / $product["products_vpe_value"] );
		
		$vpe  = $this->oCurreny["symbol_left"];
		$vpe .= number_format( $price , 2);
		$vpe .= $this->oCurreny["symbol_right"];
		$vpe .= TXT_PER;
		$vpe .= $product["products_vpe_name"];
			
		return $vpe;
	}

	/**
	 * Function to Parse Options like [TAB:xxxx] in the Description
	 * @param string $descrtiption
	 */
	private function parseDescription($descrtiption) {
		$this->removeTagsFromString($descrtiption, array("IMG"));

		$tabs = array();
		$_tabs = preg_match_all("/\[TAB:[\w\s\d]*\]/", $descrtiption, $tabs);

		foreach($tabs[0] as $replace) {
			$replacement = preg_replace("/(\[TAB:)|\]/", "", $replace);
			$replacement = "<h1>".$replacement."</h1>";
				
			$descrtiption = preg_replace("/".preg_quote($replace)."/", $replacement, $descrtiption);
		}

		return $descrtiption;
	}

	/**
	 * Exportiere alle Produktbilder
	 *
	 * @param string $product
	 */
	private function getProductsImages($product) {
		$qry = "
			SELECT *
			FROM ".TABLE_PRODUCTS_IMAGES."
			WHERE products_id = '".$product["products_id"]."'
			ORDER BY image_nr
		";

		$httpServer = HTTP_SERVER;
		if(empty($httpServer)) $httpServer = "http://".$_SERVER["HTTP_HOST"];
		
		$images = array();
		$images[] = $httpServer.DIR_WS_CATALOG.DIR_WS_ORIGINAL_IMAGES.$product['products_image'];

		$query = xtc_db_query($qry.$pg);
		while($image = xtc_db_fetch_array($query)) {
			$images[] = $httpServer.DIR_WS_CATALOG.DIR_WS_ORIGINAL_IMAGES.$image['image_name'];
		}
		
		$images = implode("||", $images);

		return $images;
	}

	private function getProductCategoriesIds($productId) {
		$catIds = array();

		$catsQry = "
					SELECT DISTINCT
					ptc.categories_id
					FROM ".TABLE_PRODUCTS_TO_CATEGORIES." ptc
					JOIN ".TABLE_CATEGORIES." c ON ptc.categories_id = c.categories_id
					WHERE ptc.products_id = $productId
					  AND c.categories_status = 1
				";

		$catsQuery = xtc_db_query($catsQry);

		while($row = xtc_db_fetch_array($catsQuery)) {
			$catIds[] = $row["categories_id"];
		}

		return $catIds;
	}

	/**
	 * Load all Categories of the product and build its category-path
	 *
	 * The categories are seperated by a =>. The Paths are seperated b< a double-pipe ||
	 *
	 * Example: kategorie_1=>kategorie_2||other_1=>other_2
	 * @param int $productId
	 * @return string
	 */
	private function getProductPath($productId) {
		$producCategories = $this->getProductCategoriesIds($productId);
		$arrAllCategories = array();

		foreach ($producCategories as $catId) {
			$cats = xtc_get_category_path($catId);
			$arrCatIds= explode('_', $cats);

			$arrCatNames = Array();
			foreach($arrCatIds AS $iCatId)
			{
				$qryCatName = xtc_db_fetch_array(xtc_db_query('
					SELECT cd.categories_name
					FROM '.TABLE_CATEGORIES_DESCRIPTION.' cd
					WHERE cd.categories_id = '.$iCatId.'
					AND cd.language_id = '.$this->languageId));
				
				$arrCatNames[] = $qryCatName['categories_name'];
			}
			$arrAllCategories[] = implode('=>',$arrCatNames);
		}

		return implode('||',$arrAllCategories);
	}

	/**
	 * Returns a array with all Variations of the Product
	 * @param int $productId
	 */
	private function getVariations($productId, $tax_rate) {
		$qry = "
			SELECT
				pa.products_attributes_id,
				po.products_options_id,
				pov.products_options_values_id,
				po.products_options_name,
				pov.products_options_values_name,
				pa.attributes_model,
				pa.options_values_price,
				pa.price_prefix,
				pa.options_values_weight,
				pa.weight_prefix,
				pa.attributes_stock,
				pov.gm_filename
			FROM ".TABLE_PRODUCTS_ATTRIBUTES." pa
			JOIN ".TABLE_PRODUCTS_OPTIONS." po ON pa.options_id = po.products_options_id
			JOIN ".TABLE_PRODUCTS_OPTIONS_VALUES_TO_PRODUCTS_OPTIONS." povtpo ON povtpo.products_options_id = po.products_options_id 
			JOIN ".TABLE_PRODUCTS_OPTIONS_VALUES." pov ON (povtpo.products_options_values_id = pov.products_options_values_id AND pa.options_values_id = pov.products_options_values_id AND pov.language_id = $this->languageId)
			WHERE pa.products_id = ".$productId."
		";

		if(ATTRIBUTE_STOCK_CHECK == 'true') {
			$qry .= " AND pa.attributes_stock > 0";
		}

		$qry .= " ORDER BY po.products_options_id DESC";

		$query = xtc_db_query($qry);

		//		$options = array_pad(array(), 5, "");
		$options = array();

		$i=-1;
		$j = 0;
		$old = 0;
		$names = array();

		if(mysql_num_rows($query) < 1 ) {
			return Array();
		}

		while($variation = xtc_db_fetch_array($query)) {
				
			if($variation["products_options_id"] != $old){
				$i++;
				$old = $variation["products_options_id"];
				$names[] = $variation["products_options_name"];
			}
			$options[$i][] = $variation;
		}

		$sg_prod_var= array();
		$countVariations = 1;
		foreach($options as $option)
		$countVariations *= count($option);
			
		if($countVariations > $this->config["max_attributes"]) {
			$this->buildOptions($sg_prod_var, $options, $tax_rate);
			$sg_prod_var["has_options"] = 1;
		} else {
			$this->buildAttributes($sg_prod_var, $options);
			$sg_prod_var["has_options"] = 0;
		}

		return $sg_prod_var;
	}


	/**
	 * Build the Productvariations recursively
	 *
	 * @param &array $sg_prod_var
	 * @param array $variations
	 * @param int $index
	 * @param array $tmp
	 */
	private function buildOptions(&$sg_prod_var, $variations, $tax_rate) {
		$tmp=array();
		$i = 0;
		foreach($variations as $_variation) {
			$i++;
			$tmp["option_$i"] = strip_tags($_variation[0]["products_options_name"]);
				
			$options = array();
			foreach($_variation as $option) {
				$optionOffsetPrice = $option["options_values_price"]*(1+$tax_rate); // Include Tax
				$optionOffsetPrice = round($optionOffsetPrice * 100, 0); // get euro-cent

				$field  = strip_tags($option["products_options_values_id"])."=".strip_tags($option["products_options_values_name"]);
				$field .= $option["options_values_price"]!=0?"=>".$option["price_prefix"].$optionOffsetPrice:"";

				$options[] = $field;
			}
			$tmp["option_".$i."_values"] = implode("||", $options);
		}
		$sg_prod_var = $tmp;
	}

	/**
	 * Build the Productvariations recursively
	 *
	 * @param &array $sg_prod_var
	 * @param array $variations
	 * @param int $index
	 * @param array $tmp
	 */
	private function buildAttributes(&$sg_prod_var, $variations, $index=0, $tmp=array()) {
		if($index == 0) {
			// Index 0 sind die Überschriften. Diese müssen als erstes hinzugefügt werden
			for($i=0;$i<count($variations);$i++)
			$sg_prod_var[0]["attribute_".($i+1)] = $variations[$i][0]["products_options_name"];
		}

		foreach($variations[$index] as $variation) {
			$tmp["attribute_".($index+1)] = $variation["products_options_values_name"];
			$tmp["order_info"]["attribute_".($index+1)] = $variation["products_attributes_id"];
				
			if(!empty($variation["gm_filename"]))
			$tmp["images"][] = HTTP_CATALOG_SERVER.DIR_WS_CATALOG.DIR_WS_IMAGES.'/product_images/attribute_images/'.$variation["gm_filename"];
				
			$tmp["stock_quantity"] = $variation["attributes_stock"];
				
			// Kalkuliere den Preisunterschied
			$price = $variation["options_values_price"];
			if($variation["price_prefix"] == "-")
			$price = -1 * $price;
			$tmp["offset_amount"] += $price;
				
				
			// Kalkuliere den Gewichtsunterschied
			$weight = (float) $variation["options_values_weight"];
			if($variation["weight_prefix"] == "-")
			$weight = -1 * $weight;
			$tmp["offset_weight"] += (double) $weight;
				
			if($index < (count($variations)-1)) {
				// Fahre mit nächstem Attribute fort
				$this->buildAttributes($sg_prod_var, $variations, $index+1, $tmp);
			} else {
				// Wenn kein Attribut mehr existiert, dieses auf den Stack legen
				$sg_prod_var[] = $tmp;
			}
			// Preis und Gewicht wieder rückgängig machen
			$tmp["offset_amount"] -= $price;
			$tmp["offset_weight"] -= $weight;
		}
	}

	private function getRelatedShopItems($products_id) {
		$qry = "
			SELECT px.xsell_id
			FROM ".TABLE_PRODUCTS_XSELL." px
			JOIN ".TABLE_PRODUCTS." p ON (px.products_id = p.products_id)
			WHERE p.products_id = $products_id
			  AND (p.products_date_available < NOW() OR p.products_date_available IS NULL)
			ORDER BY px.sort_order
		";

		$xSellIds = array();

		$query = xtc_db_query($qry);
		for($i = 0; $i < xtc_db_num_rows($query); $i++) {
			$array = xtc_db_fetch_array($query);
			$xSellIds[] = $array["xsell_id"];
		}

		return implode("||", $xSellIds);
	}

	private function buildProperties($product) {
		$properties = array();

		if(!empty($product["gm_show_qty_info"]))
		$properties[] = TXT_QTY."=>$product[gm_show_qty_info]";
		
		if(!empty($product["products_model"]))
		$properties[] = "Shop Artikel-Nr.=>$product[products_model]";
		
		if(!empty($product["products_fsk18"]) && $product["products_fsk18"] == 1)
		$properties[] = preg_replace("/\:/", "", TEXT_FSK18)."=>".TEXT_YES;
		
		if(!empty($product["gm_show_weight"]) && $product["gm_show_weight"] == 1
		&& !empty($product["products_weight"]))
		$properties[] = TXT_WEIGHT."=>$product[products_weight] kg";
			
		$properties = implode("||", $properties);
		return $properties;
	}

	/**
	 * (non-PHPdoc)
	 * @see ShopgatePluginCore::getUserData()
	 */
	public function getUserData($user, $pass) {
		$qry = "SELECT"
		. " customer.customers_id,"
		. " customer.customers_gender,"
		. " customer.customers_telephone,"
		. " customer.customers_password,"
		. " customer.customers_email_address,"
		. " address.entry_company,"
		. " address.entry_firstname,"
		. " address.entry_lastname,"
		. " address.entry_street_address,"
		. " address.entry_postcode,"
		. " address.entry_city,"
		. " address.entry_state,"
		. " country.countries_iso_code_2"
		. " FROM " . TABLE_CUSTOMERS . " AS customer"
		. " JOIN " . TABLE_ADDRESS_BOOK . " AS address"
		. " ON customer.customers_default_address_id = address.address_book_id"
		. " AND customer.customers_id = address.customers_id"
		. " JOIN " . TABLE_COUNTRIES . " AS country"
		. " ON country.countries_id = address.entry_country_id"
		. " WHERE customers_email_address = '" . xtc_db_input($user) . "'";

		$customer_query = xtc_db_query($qry);

		if (!xtc_db_num_rows($customer_query)) {
			throw new ShopgateFrameworkException("xtCommerce ShopgatePlugin - Invalid Login Data for User.");
		} else {
			$customer = xtc_db_fetch_array($customer_query);
			// Check that password is good
			if (!xtc_validate_password($pass, $customer['customers_password'])) {
				throw new ShopgateFrameworkException("xtCommerce ShopgatePlugin - Bad Password.");
			} else {
				$userData = new ShopgateShopCustomer();
				$userData->setCustomerNumber($customer["customers_id"]);
				$userData->setSurname(utf8_encode($customer["entry_lastname"]));
				$userData->setFirstName(utf8_encode($customer["entry_firstname"]));
				$userData->setCity(utf8_encode($customer["entry_city"]));
				$userData->setMobile("");
				$userData->setPhone(utf8_encode($customer["customers_telephone"]));
				$userData->setZip($customer["entry_postcode"]);
				$userData->setStreet(utf8_encode($customer["entry_street_address"]));
				$userData->setGender($customer["customers_gender"]=="m"?ShopgateShopCustomer::MALE:ShopgateShopCustomer::FEMALE);
				$userData->setCountry(utf8_encode($customer["countries_iso_code_2"]));
				$userData->setCompany(utf8_encode($customer["entry_company"]));
				$userData->setMail(utf8_encode($customer["customers_email_address"]));

				return $userData;
			}
		}

		return false;
	}

	public function createShopInfo(){
		$infos = array();
		
		$constants = array("PROJECT_VERSION", "FIRST_GX2_TEMPLATE_VERSION");

		foreach( $constants as $const ) {
			$infos[$const] = defined( $const ) ? constant( $const ) : "-";
		}
		
		return $infos;
	}

	/**
	 * @see ShopgatePluginCore::saveOrder()
	 */
	public function saveOrder(ShopgateOrder $order) {
		
		/**
		 * @var ShopgateOrderAddress
		 */
		$customer = $order->getCustomerAddress();
		/**
		 * @var ShopgateOrderAddress
		 */
		$delivery = $order->getDeliveryAddress();
		/**
		 * @var ShopgateOrderAddress
		 */
		$invoice = $order->getInvoiceAddress();

		$qry = "
			SELECT *
			FROM ".TABLE_SHOPGATE_ORDERS."
			WHERE shopgate_order_number = ".$order->getOrderNumber()."
		";

		// 		$qry = xtc_db_query($qry);
		// 		if(xtc_db_num_rows($qry) > 0) {
		// 			throw new ShopgateFrameworkException("Die Bestellung mit der Nummer ".$order->getOrderNumber()." existiert bereits.");
		// 		}

		// Finde den Kunden
		$count = xtc_db_num_rows(
		xtc_db_query(
				"SELECT * FROM ".TABLE_CUSTOMERS." WHERE customers_id = '".$order->getExternalCustomerNumber()."'"
		));

		// Dateils für die Währung
		$currency = xtc_db_fetch_array(
		xtc_db_query(
				"SELECT * FROM ".TABLE_CURRENCIES." WHERE code = '".$this->config["plugin_currency"]."' LIMIT 1"
		));

		$customerId = $order->getExternalCustomerNumber();

		if(empty($customerId))
		$customerId = $this->createGuestUser($order);
			
		$phone_number = $order->getCustomerMobile();
		if(empty($phone_number))
		$phone_number = $order->getCustomerPhone();
			
		$addressFormat = $this->getAddressFormatId();

		$importOrderStatusId = 1;

		if(defined("MODULE_PAYMENT_SHOPGATE_ORDER_STATUS_ID"))
			$importOrderStatusId = MODULE_PAYMENT_SHOPGATE_ORDER_STATUS_ID == 0 ? DEFAULT_ORDERS_STATUS_ID : MODULE_PAYMENT_SHOPGATE_ORDER_STATUS_ID;
		else if(isset($this->config["order_received_status"]))
			$importOrderStatusId = $this->config["order_received_status"];
		
			
		///////////////////////////////////////////////////////////////////////
		// Speicher die Bestellung
		///////////////////////////////////////////////////////////////////////
		$orderData = array(
			"customers_id" => $customerId,
			"customers_name" => $customer->getFirstName()." ".$customer->getSurname(),
			"customers_firstname" => $customer->getFirstName(),
			"customers_lastname" => $customer->getSurname(),
			"customers_company"=>$customer->getCompany(),
			"customers_street_address"=>$customer->getStreet(),
			"customers_suburb"=>"",
			"customers_city"=>$customer->getCity(),
			"customers_postcode"=>$customer->getZipcode(),
			"customers_state"=>"",
			"customers_country"=> $this->getCountry($customer->getCountry()),
			"customers_telephone"=>$phone_number,
			"customers_email_address"=>$order->getCustomerMail(),
			"customers_address_format_id"=>$addressFormat,
			"delivery_name"=>$delivery->getFirstName()." ".$delivery->getSurname(),
			"delivery_firstname"=>$delivery->getFirstName(),
			"delivery_lastname"=>$delivery->getSurname(),
			"delivery_company"=>$delivery->getCompany(),
			"delivery_street_address"=>$delivery->getStreet(),
			"delivery_suburb"=>"",
			"delivery_city"=>$delivery->getCity(),
			"delivery_postcode"=>$delivery->getZipcode(),
			"delivery_state"=>"",
			"delivery_country"=>$this->getCountry($delivery->getCountry()),
			"delivery_address_format_id"=>$addressFormat,
			"billing_name"=>$invoice->getFirstName()." ".$invoice->getSurname(),
			"billing_firstname"=>$invoice->getFirstName(),
			"billing_lastname"=>$invoice->getSurname(),
			"billing_company"=>$invoice->getCompany(),
			"billing_street_address"=>$invoice->getStreet(),
			"billing_suburb"=>"",
			"billing_city"=>$invoice->getCity(),
			"billing_postcode"=>$invoice->getZipcode(),
			"billing_state"=>"",
			"billing_country"=>$this->getCountry($invoice->getCountry()),
			"billing_address_format_id"=>$addressFormat,
			"payment_method"=>"shopgate",
			"cc_type"=>"",
			"cc_owner"=>"",
			"cc_number"=>"",
			"cc_expires"=>"",
			"language"=>"german",
			"last_modified"=>null,
			"date_purchased"=>date( 'Y-m-d H:i:s' ),
			"orders_status"=>$importOrderStatusId,
			"orders_date_finished"=>null,
			"currency"=>$this->config["plugin_currency"],
			"currency_value"=>$currency["value"],
		);

		foreach ($orderData as &$data) $data = utf8_decode($data);

		// Speichere die Bestellung
		xtc_db_perform(TABLE_ORDERS,$orderData);
		$orderId = xtc_db_insert_id();

		if($this->shopgateOrderTableExists()) {
			$ordersShopgateOrder = array(
				"orders_id" => $orderId,
				"shopgate_order_number" => $order->getOrderNumber(),
				"last_response" => ShopgateOrderApi::$lastResponseString,
				"modified" => "now()",
				"created" => "now()",
			);
				
			xtc_db_perform(TABLE_SHOPGATE_ORDERS, $ordersShopgateOrder);
		}

		$taxes = array();

		///////////////////////////////////////////////////////////////////////
		// Speicher die Produkte
		///////////////////////////////////////////////////////////////////////
		foreach($order->getOrderItems() as $orderItem) {
				
			$order_infos = $orderItem->getInternalOrderInfo();
			$order_infos = json_decode($order_infos, true);
				
			$item_number = $orderItem->getItemNumber();
			if(isset($order_infos["base_item_number"]))
			$item_number = $order_infos["base_item_number"];

			// Hole das Product aus der Datenbank
			$qry = xtc_db_query(
			"SELECT * FROM ".TABLE_PRODUCTS . " WHERE"
			. " products_id = '" . $item_number ."'"
			. " LIMIT 1");
				
			$dbProduct = xtc_db_fetch_array($qry);
			if(empty($dbProduct)) continue; //Fehler
				
			$productData = array(
				"orders_id"=>$orderId,
				"products_model"=>$dbProduct["products_model"],
				"products_id"=>$item_number,
				"products_name"=>xtc_db_prepare_input(utf8_decode($orderItem->getName())),
				"products_price"=> $orderItem->getUnitAmount()/100,
				"final_price"=> $orderItem->getQuantity() * ( $orderItem->getUnitAmountWithTax() / 100 ),
				"products_tax"=> $orderItem->getTaxPercent(),
				"products_quantity"=> $orderItem->getQuantity(),
				"allow_tax"=>true,
			);

			$qry = xtc_db_perform(TABLE_ORDERS_PRODUCTS, $productData);
			$produtcsOrderId = xtc_db_insert_id();
				
			if($orderItem->getHasOptions()) {
				foreach($orderItem->getOptions() as $option) {
					$attribute_model = $option->getValueNumber();
						
					// Hole das Attribut aus der Datenbank
					$qry = "
						SELECT
							po.products_options_name,
							pov.products_options_values_name,
							pa.options_values_price,
							pa.price_prefix
						FROM ".TABLE_PRODUCTS_ATTRIBUTES." pa
						JOIN ".TABLE_PRODUCTS_OPTIONS." po ON pa.options_id = po.products_options_id
						JOIN ".TABLE_PRODUCTS_OPTIONS_VALUES_TO_PRODUCTS_OPTIONS." povtpo ON povtpo.products_options_id = po.products_options_id 
						JOIN ".TABLE_PRODUCTS_OPTIONS_VALUES." pov ON (povtpo.products_options_values_id = pov.products_options_values_id AND pa.options_values_id = pov.products_options_values_id AND pov.language_id = $this->languageId)
						WHERE pa.products_id = ".$dbProduct["products_id"]."
						 AND pa.options_values_id = ".$attribute_model."
						LIMIT 1
					";

					$qry = xtc_db_query($qry);
					$dbattribute = xtc_db_fetch_array($qry);
					if(empty($dbattribute)) continue; //Fehler
					$productAttributeData = array(
						"orders_id"=>$orderId,
						"orders_products_id"=>$produtcsOrderId,
						"products_options"=>$dbattribute["products_options_name"],
						"products_options_values"=>$dbattribute["products_options_values_name"],
						"options_values_price"=>$dbattribute["options_values_price"],
						"price_prefix"=>$dbattribute["price_prefix"],
					);
					xtc_db_perform(TABLE_ORDERS_PRODUCTS_ATTRIBUTES, $productAttributeData);
				}
			} else {
				for($i=1;$i<=5;$i++) {
					if(!isset($order_infos["attribute_$i"]))
					break;
					$attribute_model = $order_infos["attribute_$i"];
						
					// Hole das Attribut aus der Datenbank
					$qry = xtc_db_query("
						SELECT
							po.products_options_name,
							pov.products_options_values_name,
							pa.options_values_price,
							pa.price_prefix
						FROM ".TABLE_PRODUCTS_ATTRIBUTES." pa
						JOIN ".TABLE_PRODUCTS_OPTIONS." po ON pa.options_id = po.products_options_id
						JOIN ".TABLE_PRODUCTS_OPTIONS_VALUES_TO_PRODUCTS_OPTIONS." povtpo ON povtpo.products_options_id = po.products_options_id 
						JOIN ".TABLE_PRODUCTS_OPTIONS_VALUES." pov ON (povtpo.products_options_values_id = pov.products_options_values_id AND pa.options_values_id = pov.products_options_values_id AND pov.language_id = $this->languageId)
						WHERE pa.products_id = ".$dbProduct["products_id"]."
						 AND pa.products_attributes_id = ".$attribute_model."
						LIMIT 1
					");
						
					$dbattribute = xtc_db_fetch_array($qry);
					if(empty($dbattribute)) continue; //Fehler
						
					$productAttributeData = array(
						"orders_id"=>$orderId,
						"orders_products_id"=>$produtcsOrderId,
						"products_options"=>$dbattribute["products_options_name"],
						"products_options_values"=>$dbattribute["products_options_values_name"],
						"options_values_price"=>$dbattribute["options_values_price"],
						"price_prefix"=>$dbattribute["price_prefix"],
					);
					xtc_db_perform(TABLE_ORDERS_PRODUCTS_ATTRIBUTES, $productAttributeData);
				}
			}
				
			$taxRate = $orderItem->getTaxPercent();
			$tax = ( $orderItem->getUnitAmountWithTax() - $orderItem->getUnitAmount() ) / 100 ;
			$taxClass = xtc_get_tax_class_id($dbProduct["products_id"]);
				
			if(!isset($taxes[$taxClass])) {
				$taxes[$taxClass] = array(
								"class" => $taxClass,
								"rate" => $taxRate,
								"value" => $tax);
			} else {
				$taxes[$taxClass]["value"] += $tax;
			}
				
			$this->updateItemsStock($order);
		}


		///////////////////////////////////////////////////////////////////////
		// Speicher den Gesamtbetrag
		///////////////////////////////////////////////////////////////////////
		$amountWithTax = $order->getAmountItems()/100;
		 
		 
		$amount = $amountWithTax/(1+($tax_rate/100));
		$tax_amount = $amountWithTax-$amount;
		 
		$xtPrice = new xtcPrice($this->config["plugin_currency"], 1);
		$shippingCosts = ($order->getAmountShipping() / 100);
		 
		$fullAmount = $amountWithTax + $shippingCosts;
		 
		$ordersTotal = array();
		$ordersTotal[] = array(
				"orders_id"=>$orderId,
				"title"=>xtc_db_prepare_input("Zwischensumme:"),
				"text"=>$xtPrice->xtcFormat($amountWithTax, true),
				"value"=>$order->getAmountItems()/100,
				"class"=>"ot_subtotal",
				"sort_order"=>10
		);
		$ordersTotal[] = array(
				"orders_id"=>$orderId,
				"title"=>xtc_db_prepare_input("Versand:"),
				"text"=>$xtPrice->xtcFormat($shippingCosts, true),
				"value"=>$shippingCosts,
				"class"=>"ot_shipping",
				"sort_order"=>15
		);

		// Liste alle MwSt. auf und deren Höhe
		foreach($taxes as $taxClass => $tax) {
			$tax_description = xtc_get_tax_description($taxClass);
				
			$ordersTotals = array();
			$ordersTotals['orders_id']  = $orderId;
			$ordersTotals['title']      = "inkl. " . $tax_description;
			$ordersTotals['text']       = $xtPrice->xtcFormat($tax['value'], true);
			$ordersTotals['value']      = $tax['value'];
			$ordersTotals['class']      = 'ot_tax';
			$ordersTotals['sort_order'] = $i++;
			$ordersTotal[] = $ordersTotals;
				
		}

		$ordersTotal[] = array(
				"orders_id"=>$orderId,
				"title"=>"<b>Summe:</b>",
				"text"=>"<b>".$xtPrice->xtcFormat($fullAmount, true)."</b>",
				"value"=>$amountWithTax,
				"class"=>"ot_total",
				"sort_order"=>99
		);

		foreach($ordersTotal as $ot)
		xtc_db_perform(TABLE_ORDERS_TOTAL,$ot);
			

		///////////////////////////////////////////////////////////////////////
		// Speicher Kommentare zur Bestellung in der Historie
		///////////////////////////////////////////////////////////////////////
		$histories = array(
		array(
				"orders_id"=> $orderId,
				"orders_status_id"=>$importOrderStatusId,
				"date_added"=>date( 'Y-m-d H:i:s'),
				"customer_notified"=>true,
				"comments"=>xtc_db_prepare_input(utf8_decode(
		($order->getIsTest()?"#### DIES IST EINE TESTBESTELLUNG ####\n":"").
				"Bestellung durch Shopgate hinzugefügt."
		)),
		),
		array(
				"orders_id"=> $orderId,
				"orders_status_id"=>$importOrderStatusId,
				"date_added"=>date( 'Y-m-d H:i:s'),
				"customer_notified"=>false,
				"comments"=>xtc_db_prepare_input(utf8_decode("BestNr: ".$order->getOrderNumber())),
		),
		);
		foreach($histories as $history)
		xtc_db_perform(TABLE_ORDERS_STATUS_HISTORY,$history);
	}

	private function updateItemsStock(ShopgateOrder $order) {
		if(STOCK_LIMITED != "true") return; /* STOCK_LIMITED ist vom GambioSystem gegeben */

		foreach($order->getOrderItems() as $item) {
			$options = json_decode($item->getInternalOrderInfo(), true);
				
			$productId = $item->getItemNumber();
				
			if(!empty($options) && isset($options["base_item_number"])) {
				$productId = $options["base_item_number"];
			}
				
			$stock = xtc_get_products_stock($productId);
			$stock -= $item->getQuantity();
				
			$data = array(
				"products_quantity" => $stock,
			);
				
			if($stock <= 0 && STOCK_CHECK == "true" && STOCK_LIMITED == "true" && STOCK_ALLOW_CHECKOUT != "true") {
				$data["products_status"] = 0;
			}
			xtc_db_perform(TABLE_PRODUCTS, $data, "update", "products_id = $productId");
		}
	}

	protected function createReviewsCsv() {
		$qry = "
		SELECT
		r.reviews_id,
		r.reviews_rating,
		r.customers_name,
		r.products_id,
		r.reviews_rating,
		r.date_added,
		rd.reviews_text
		FROM ".TABLE_REVIEWS." r
		JOIN ".TABLE_REVIEWS_DESCRIPTION." rd ON rd.reviews_id = r.reviews_id
		WHERE rd.languages_id = 2
		";
		$qry = xtc_db_query($qry);
		while($review = xtc_db_fetch_array($qry)) {
			$item = array(
				"item_number" => $review["products_id"],
				"update_review_id" => $review["reviews_id"],
				"score" => $review["reviews_rating"],
				"name" => $review["customers_name"],
				"date" => $review["date_added"],
				"text" => $review["reviews_text"],
			);
			$this->addItem($item);
		}
	}

	protected function createPagesCsv() {
	}

	private function getAddressFormatId() {
		$qry = "
			SELECT c.address_format_id
			FROM ".TABLE_COUNTRIES." c
			WHERE UPPER(c.countries_iso_code_2) = 'DE'
		";

		$item = xtc_db_fetch_array(xtc_db_query($qry));
		return $item["address_format_id"];
	}

	private function getCountry($isoCode) {
		$qry = "
			SELECT *
			FROM ".TABLE_COUNTRIES." c
			WHERE UPPER(c.countries_iso_code_2) = UPPER('$isoCode')
		";

		$item = xtc_db_fetch_array(xtc_db_query($qry));

		return $item["countries_name"];
	}

	private function shopgateOrderTableExists() {
		$tableExists = false;

		$query = xtc_db_query("SHOW TABLES");
		$max = xtc_db_num_rows($query);
		for($i=0; $i<$max; $i++) {
			$array = xtc_db_fetch_array($query);
			$array = array_values($array);
			if($array[0] == "orders_shopgate_order") {
				$tableExists = true;
				break;
			}
		}

		return $tableExists;
	}

	private function createGuestUser(ShopgateOrder $order) {
		//		$order = new ShopgateOrder();
		$address = $order->getCustomerAddress();

		$customerStatus = $this->config["customers_status_id"];
		if($customerStatus === -1) $customerStatus = DEFAULT_CUSTOMERS_STATUS_ID;
		
		$customer = array();
		$customer["customers_vat_id_status"] = 0;
		$customer["customers_status"] = $customerStatus;
		$customer["customers_gender"] =  $address->getGender();
		$customer["customers_firstname"] = utf8_decode($address->getFirstName());
		$customer["customers_lastname"] = utf8_decode($address->getSurname());
		$customer["customers_email_address"] = utf8_decode($order->getCustomerMail());
		$customer["customers_default_address_id"] = "";
		$customer["customers_telephone"] = utf8_decode($order->getCustomerPhone());
		$customer["customers_fax"] = utf8_decode($order->getCustomerFax());
		//		$customer["customers_password"] = "";
		$customer["customers_newsletter"] = 0;
		$customer["customers_newsletter_mode"] = 0;
		$customer["member_flag"] = 0;
		$customer["delete_user"] = 1;
		$customer["account_type"] = 0;
		$customer["refferers_id"] = 0;
		$customer["customers_date_added"] = date( 'Y-m-d H:i:s' );
		$customer["customers_last_modified"] = date( 'Y-m-d H:i:s' );

		xtc_db_perform(TABLE_CUSTOMERS, $customer);
		$customerId = xtc_db_insert_id();

		$qry = "SELECT countries_id FROM ".TABLE_COUNTRIES." WHERE UPPER(countries_iso_code_2) = UPPER('".$address->getCountry() ."')";
		$qry = xtc_db_query($qry);
		$country = xtc_db_fetch_array($qry);
		$country = $country["countries_id"];

		$_address = array(
			"customers_id" => $customerId,
			"entry_gender" => utf8_decode($address->getGender()),
			"entry_company" => utf8_decode($address->getCompany()),
			"entry_firstname" => utf8_decode($address->getFirstName()),
			"entry_lastname" => utf8_decode($address->getSurname()),
			"entry_street_address" => utf8_decode($address->getStreet()),
			"entry_suburb" => "",
			"entry_postcode" => utf8_decode($address->getZipcode()),
			"entry_city" => utf8_decode($address->getCity()),
			"entry_state" => "",
			"entry_country_id" => 81,
			"entry_zone_id" => null,
			"address_date_added" => date( 'Y-m-d H:i:s' ),
			"address_last_modified" => date( 'Y-m-d H:i:s' ),
		);
		xtc_db_perform(TABLE_ADDRESS_BOOK, $_address);
		$addressId = xtc_db_insert_id();

		$customer = array(
			"customers_default_address_id" =>$addressId 
		);
		xtc_db_perform(TABLE_CUSTOMERS, $customer, "update", "customers_id = $customerId");

		$_info = array(
			"customers_info_id" => $customerId,
			"customers_info_date_of_last_logon" => date( 'Y-m-d H:i:s' ),
			"customers_info_number_of_logons" => '1',
			"customers_info_date_account_created" => date( 'Y-m-d H:i:s' ),
			"customers_info_date_account_last_modified" => date( 'Y-m-d H:i:s' ),
			"global_product_notifications" => 0
		);
		xtc_db_perform(TABLE_CUSTOMERS_INFO, $_info);

		return $customerId;
	}
}
